import psycopg2
import psycopg2.extras
import sys
import gdal
from gdalconst import *
import os
from shapely.wkt import dumps, loads
import datetime
from datetime import timedelta
import numpy as np

def join_mod_temp(time_range):
    f = open("result/mod4.csv", "w+")
    station_query='SELECT id, ST_AsText(location) as location FROM public.grdstation WHERE id>=1 and id <=5'
    #mod_query="SELECT mod04.aqstime, mod04.aod, mod07temp.temperature from (SELECT aqstime, st_neighborhood(rasref10, (st_GeomFromText('POINT({0} {1})', 4326)), 2, 2, true) as aod FROM apom.satresampmod04 where aqstime < '2014-09-10 00:00:00'::timestamp) as mod04 inner join (SELECT aqstime, st_neighborhood(rasref10, (st_GeomFromText('POINT({2} {3})', 4326)), 2, 2, true) as temperature FROM apom.satresampmod07temperature where aqstime < '2014-09-10 00:00:00'::timestamp) as mod07temp ON (mod04.aqstime = mod07temp.aqstime) order by mod04.aqstime asc"
    mod_query="SELECT mod04.aqstime, mod04.aod, mod07temp.temperature from (SELECT aqstime, st_neighborhood(rasref10, (st_GeomFromText('POINT({0} {1})', 4326)), 2, 2, true) as aod FROM apom.satresampmod04 where aqstime < '2014-09-10 00:00:00'::timestamp) as mod04 inner join (SELECT aqstime, ST_Value(rasref10, (st_GeomFromText('POINT({2} {3})', 4326)),true) as temperature FROM apom.satresampmod07temperature where aqstime < '2014-09-10 00:00:00'::timestamp) as mod07temp ON (mod04.aqstime = mod07temp.aqstime) order by mod04.aqstime asc"
                                                  
    pm_query="SELECT avg(pm1), avg(pm25), avg(pm10) FROM apom.grdpmhiscem_data WHERE (pm1 > 0 OR pm25 > 0 OR pm10 > 0) and aqstime > '{0}'::timestamp - interval '{1} minutes' and aqstime < '{2}'::timestamp + interval '{3} minutes' and stationid = {4} GROUP BY aqstime"
    con = None
    try:
        # doc danh sach tram
        con = psycopg2.connect(host='192.168.3.4', database='fimo', user='rasdaman', password='rasdaman') 
        cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
        cur.execute(station_query)
        stations = cur.fetchall()
        for station in stations:
            stationid = station["id"]
            # bo qua tram Khanh Hoa
            # if stationid  == 3:
            #     continue
                
            location = loads(station["location"])
        
            cur.execute(mod_query.format(location.x, location.y, location.x, location.y))

         
            rows = cur.fetchall()
            for row in rows:
                aqstime = datetime.datetime.strptime(str(row["aqstime"]), "%Y-%m-%d %H:%M:%S") + timedelta(hours=7)
        
                # du lieu gia ve la 1 mang 2 chieu
                aod = row["aod"]
                aod_result = []
                for i in range(len(aod)):
                    # filter nhung gia tri != None thi se them vao mang ket qua
                    tmp = filter(lambda a: a != None, aod[i])
                    aod_result += tmp
                # calculate real aod value
                aod_value = (np.median(aod_result) * 0.00100000004749745) if len(aod_result) > 0 else 0
                    
                
                temperature = row["temperature"]
                if(temperature!=None):
                    temperature_value = (temperature + 15000) * 0.00999999977648258
                else:
                    temperature_value=0;
                    
#                 temperature = row["temperature"]
#                 temperature_result = []
#                 for i in range(len(temperature)):
#                     tmp = filter(lambda a: a != None, temperature[i])
#                     temperature_result += tmp
#                 temperature_value = (np.median(temperature_result) + 15000) * 0.00999999977648258 if len(temperature_result) > 0 else 0
#                
                if temperature_value > 0 and aod_value > 0:
                
                    cur.execute(pm_query.format(aqstime, time_range, aqstime, time_range, stationid))
                    
                    pm_row = cur.fetchone()
                    if not(pm_row is None):
                        pm1 = pm_row[0]
                        pm25 = pm_row[1]
                        pm10 = pm_row[2]
                     

                        f.write("{0},{1},{2},{3},{4},{5},{6},{7},{8}\n".format(stationid, aqstime.year, aqstime.month, aqstime, pm1, pm25, pm10, aod_value, temperature_value))
            
    except psycopg2.DatabaseError, e:
        print 'Error %s' % e    
        sys.exit(1)
    finally:
        if con:
            con.close()
        if f:
            f.close()
def join_myd_temp(time_range):
    f = open("result/myd4.csv", "w+")
    station_query='SELECT id, ST_AsText(location) as location FROM public.grdstation WHERE id>=1 and id <=5'
    #mod_query="SELECT myd04.aqstime, myd07temp.aqstime as temp_aqstime, myd04.aod, myd07temp.temperature from (SELECT aqstime, st_neighborhood(rasref10, (st_GeomFromText('POINT({0} {1})', 4326)), 2, 2, true) as aod FROM apom.satresampmyd04 where aqstime < '2014-09-10 00:00:00'::timestamp) as myd04 inner join (SELECT aqstime, st_neighborhood(rasref10, (st_GeomFromText('POINT({2} {3})', 4326)), 2, 2, true) as temperature FROM apom.satresampmyd07temperature where aqstime < '2014-09-10 00:00:00'::timestamp) as myd07temp ON (myd04.aqstime = myd07temp.aqstime) order by myd04.aqstime asc"
    mod_query="SELECT myd04.aqstime, myd04.aod, myd07temp.temperature from (SELECT aqstime, st_neighborhood(rasref10, (st_GeomFromText('POINT({0} {1})', 4326)), 2, 2, true) as aod FROM apom.satresampmyd04 where aqstime < '2014-09-10 00:00:00'::timestamp) as myd04 inner join (SELECT aqstime, ST_Value(rasref10, (st_GeomFromText('POINT({2} {3})', 4326)),true) as temperature FROM apom.satresampmyd07temperature where aqstime < '2014-09-10 00:00:00'::timestamp) as myd07temp ON (myd04.aqstime = myd07temp.aqstime) order by myd04.aqstime asc"
  
    pm_query="SELECT avg(pm1), avg(pm25), avg(pm10) FROM apom.grdpmhiscem_data WHERE (pm1 > 0 OR pm25 > 0 OR pm10 > 0) and aqstime > '{0}'::timestamp - interval '{1} minutes' and aqstime < '{2}'::timestamp + interval '{3} minutes' and stationid = {4} GROUP BY aqstime"
    con = None
    try:
        # doc danh sach tram
        con = psycopg2.connect(host='192.168.3.4', database='fimo', user='rasdaman', password='rasdaman') 
        cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
        cur.execute(station_query)
        
        stations = cur.fetchall()
        for station in stations:
            stationid = station["id"]
            # bo qua tram Khanh Hoa
            # if stationid  == 3:
            #     continue
                
            location = loads(station["location"])
        
            cur.execute(mod_query.format(location.x, location.y, location.x, location.y))

         
            rows = cur.fetchall()
            for row in rows:
                aqstime = datetime.datetime.strptime(str(row["aqstime"]), "%Y-%m-%d %H:%M:%S") + timedelta(hours=7)
              
                # du lieu gia ve la 1 mang 2 chieu
                aod = row["aod"]
                aod_result = []
                for i in range(len(aod)):
                    # filter nhung gia tri != None thi se them vao mang ket qua
                    tmp = filter(lambda a: a != None, aod[i])
                    aod_result += tmp
                # calculate real aod value
                aod_value = (np.median(aod_result) * 0.00100000004749745) if len(aod_result) > 0 else 0
                    
                temperature = row["temperature"]
                if(temperature!=None):
                    temperature_value = (temperature + 15000) * 0.00999999977648258
                else:
                    temperature_value=0;
                    
#                 temperature = row["temperature"]
#                 temperature_result = []
#                 for i in range(len(temperature)):
#                     tmp = filter(lambda a: a != None, temperature[i])
#                     temperature_result += tmp
#                 temperature_value = (np.median(temperature_result) + 15000) * 0.00999999977648258 if len(temperature_result) > 0 else 0

                #pressure = row["pressure"]
                #pressure_result = []
                # for i in range(len(pressure)):
                #     tmp = filter(lambda a: a != None, pressure[i])
                #     pressure_result += tmp
                # pressure_value = np.median(pressure_result) if len(pressure_result) > 0 else 0

                if temperature_value > 0 and aod_value > 0:
            
                    cur.execute(pm_query.format(aqstime, time_range, aqstime, time_range, stationid))
                    
                    pm_row = cur.fetchone()
                    if not(pm_row is None):
                        pm1 = pm_row[0]
                        pm25 = pm_row[1]
                        pm10 = pm_row[2]
                        f.write("{0},{1},{2},{3},{4},{5},{6},{7},{8}\n".format(stationid, aqstime.year, aqstime.month, aqstime, pm1, pm25, pm10, aod_value, temperature_value))
            
    except psycopg2.DatabaseError, e:
        print 'Error %s' % e    
        sys.exit(1)
    finally:
        if con:
            con.close()
        if f:
            f.close()
def join_mod_temp_press(time_range):
    f = open("data/mod.csv", "w+")
    con = None
    try:
        # doc danh sach tram
        con = psycopg2.connect(host='192.168.3.4', database='fimo', user='rasdaman', password='rasdaman') 
        cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
        cur.execute('SELECT id, ST_AsText(location) as location FROM public.grdstation WHERE type = 2')
        stations = cur.fetchall()
        for station in stations:
            stationid = station["id"]
            # bo qua tram Khanh Hoa
            #if stationid  == 3:
            #   continue
                
            location = loads(station["location"])
         
          
            cur.execute("SELECT mod04.aqstime, mod07temp.aqstime as temp_aqstime, mod07press.aqstime as press_aqstime,mod04.aod, mod07temp.temperature,mod07press.pressure from (SELECT aqstime, st_neighborhood(rasref, (st_GeomFromText('POINT({0} {1})', 4326)), 2, 2, true) as aod FROM apom.satresampmod04 where aqstime > '2010-01-01 00:00:00'::timestamp) as mod04 inner join (SELECT aqstime, ST_Value(rasref, (st_GeomFromText('POINT({2} {3})', 4326)),true) as temperature FROM apom.satresampmod07temperature where aqstime > '2010-01-01 00:00:00'::timestamp) as mod07temp ON (mod04.aqstime = mod07temp.aqstime) inner join(SELECT aqstime, ST_Value(rasref, (st_GeomFromText('POINT({4} {5})', 4326)),true) as pressure FROM apom.satresampmod07pressure where aqstime > '2010-01-01 00:00:00'::timestamp) as mod07press ON (mod04.aqstime = mod07press.aqstime) order by mod04.aqstime asc".format(location.y, location.x, location.y, location.x,location.y, location.x))                                                                                                                                                                                                                   
          
          
          
          
            rows = cur.fetchall()
            for row in rows:
                aqstime = datetime.datetime.strptime(str(row["aqstime"]), "%Y-%m-%d %H:%M:%S") + timedelta(hours=7)
                temp_aqstime = datetime.datetime.strptime(str(row["temp_aqstime"]), "%Y-%m-%d %H:%M:%S") + timedelta(hours=7)
                press_aqstime = datetime.datetime.strptime(str(row["press_aqstime"]), "%Y-%m-%d %H:%M:%S") + timedelta(hours=7)

                # du lieu gia ve la 1 mang 2 chieu
                aod = row["aod"]
                aod_result = []
                for i in range(len(aod)):
                    # filter nhung gia tri != None thi se them vao mang ket qua
                    tmp = filter(lambda a: a != None, aod[i])
                    aod_result += tmp
                # calculate real aod value
                aod_value = (np.median(aod_result) * 0.00100000004749745) if len(aod_result) > 0 else 0
                    
                temperature = row["temperature"]
                if(temperature!=None):
                    temperature_value = (temperature + 15000)* 0.00999999977648258
                else:
                    temperature_value=0;
                """
               
                temperature_result = []
                for i in range(len(temperature)):
                    tmp = filter(lambda a: a != None, temperature[i])
                    temperature_result += tmp
                temperature_value = (np.median(temperature_result) + 15000) * 0.00999999977648258 if len(temperature_result) > 0 else 0
                """
                
                pressure = row["pressure"]
                if(pressure!=None):
                    pressure_value = pressure
                else:
                    pressure_value=0;

                
                #pressure_result = []
                # for i in range(len(pressure)):
                #     tmp = filter(lambda a: a != None, pressure[i])
                #     pressure_result += tmp
                # pressure_value = np.median(pressure_result) if len(pressure_result) > 0 else 0

                if temperature_value > 0 and aod_value > 0 and pressure_value > 0:
                    #limit temp and presure
                    #cur.execute("SELECT avg(pm1), avg(pm25), avg(pm10) FROM apom.grdpmhiscem_data WHERE (pm1 > 0 OR pm25 > 0 OR pm10 > 0) and aqstime > '{0}'::timestamp - interval '{1} minutes' and aqstime < '{2}'::timestamp + interval '{3} minutes' and stationid = {4} GROUP BY aqstime".format(aqstime, time_range, aqstime, time_range, stationid))
                    # unlimit
                    cur.execute("SELECT avg(pm1), avg(pm25), avg(pm10),avg(temp), avg(barometer),avg(radiation),avg(windspd),avg(winddir) FROM apom.grdpmhiscem_data WHERE (pm1 > 0 OR pm25 > 0 OR pm10 > 0) and aqstime > '{0}'::timestamp - interval '{1} minutes' and aqstime < '{2}'::timestamp + interval '{3} minutes' and stationid = {4} GROUP BY aqstime".format(aqstime, time_range, aqstime, time_range, stationid))
                    
                    pm_row = cur.fetchone()
                    if not(pm_row is None):
                        pm1 = pm_row[0]
                        pm25 = pm_row[1]
                        pm10 = pm_row[2]
                        temp = pm_row[3]
                        barometer=pm_row[4]
                        radiation = pm_row[5]
                        windspd = pm_row[6]
                        winddir = pm_row[7]

                        f.write("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15}\n".format(stationid, aqstime.year, aqstime.month, aqstime, temp_aqstime, pm1, pm25, pm10, aod_value, temperature_value,temp,pressure_value,barometer,radiation,windspd,winddir))
            
    except psycopg2.DatabaseError, e:
        print 'Error %s' % e    
        sys.exit(1)
    finally:
        if con:
            con.close()
        if f:
            f.close()
def join_myd_temp_press(time_range):
    f = open("data/myd.csv", "w+")
    con = None
    try:
        # doc danh sach tram
        con = psycopg2.connect(host='192.168.3.4', database='fimo', user='rasdaman', password='rasdaman') 
        cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
        cur.execute('SELECT id, ST_AsText(location) as location FROM public.grdstation WHERE type = 2')
        stations = cur.fetchall()
        for station in stations:
            stationid = station["id"]
            # bo qua tram Khanh Hoa
            # if stationid  == 3:
            #     continue
                
            location = loads(station["location"])
            # lay cac pixel xung quanh tram tren anh mod04,07
            #cur.execute("SELECT mod04.aqstime, mod07temp.aqstime as temp_aqstime, mod07press.aqstime as press_aqstime, mod04.aod, mod07temp.temperature, mod07press.pressure from (SELECT aqstime, st_neighborhood(rasref, (st_GeomFromText('POINT({0} {1})', 4326)), 2, 2, true) as aod FROM apom.satresampmod04 where aqstime > '2010-01-01 00:00:00'::timestamp) as mod04 inner join (SELECT aqstime, st_neighborhood(rasref, (st_GeomFromText('POINT({2} {3})', 4326)), 2, 2, true) as temperature FROM apom.satresamptemperature where aqstime > '2010-01-01 00:00:00'::timestamp) as mod07temp ON (mod04.aqstime = mod07temp.aqstime) INNER JOIN  (SELECT aqstime, st_neighborhood(rasref, (st_GeomFromText('POINT({4} {5})', 4326)), 2, 2, true) as pressure FROM apom.satresampressure where aqstime > '2010-01-01 00:00:00'::timestamp) as mod07press ON (mod04.aqstime = mod07press.aqstime) order by mod04.aqstime asc".format(location.y, location.x, location.y, location.x, location.y, location.x))
            
            #old
            #cur.execute("SELECT mod04.aqstime, mod07temp.aqstime as temp_aqstime, mod04.aod, mod07temp.temperature from (SELECT aqstime, st_neighborhood(rasref, (st_GeomFromText('POINT({0} {1})', 4326)), 2, 2, true) as aod FROM apom.satresampmod04 where aqstime > '2010-01-01 00:00:00'::timestamp and aqstime < '2014-03-31 00:00:00'::timestamp) as mod04 inner join (SELECT aqstime, st_neighborhood(rasref, (st_GeomFromText('POINT({2} {3})', 4326)), 2, 2, true) as temperature FROM apom.satresampmod07temperature where aqstime > '2010-01-01 00:00:00'::timestamp and aqstime < '2014-03-31 00:00:00'::timestamp) as mod07temp ON (mod04.aqstime = mod07temp.aqstime) order by mod04.aqstime asc".format(location.y, location.x, location.y, location.x))

            #new temp
            #cur.execute("SELECT mod04.aqstime, mod07temp.aqstime as temp_aqstime, mod04.aod, mod07temp.temperature from (SELECT aqstime, st_neighborhood(rasref, (st_GeomFromText('POINT({0} {1})', 4326)), 2, 2, true) as aod FROM apom.satresampmod04 where aqstime > '2010-01-01 00:00:00'::timestamp) as mod04 inner join (SELECT aqstime, ST_Value(rasref, (st_GeomFromText('POINT({2} {3})', 4326)),true) as temperature FROM apom.satresampmod07temperature where aqstime > '2010-01-01 00:00:00'::timestamp) as mod07temp ON (mod04.aqstime = mod07temp.aqstime) order by mod04.aqstime asc".format(location.y, location.x, location.y, location.x))                                                                                                                                                                                                                   
            
            #new temp presure
            #cur.execute("SELECT mod04.aqstime, mod07temp.aqstime as temp_aqstime, mod07press.aqstime as press_aqstime,mod04.aod, mod07temp.temperature,mod07press.pressure from (SELECT aqstime, st_neighborhood(rasref, (st_GeomFromText('POINT({0} {1})', 4326)), 2, 2, true) as aod FROM apom.satresampmod04 where aqstime > '2010-01-01 00:00:00'::timestamp) as mod04 inner join (SELECT aqstime, ST_Value(rasref, (st_GeomFromText('POINT({2} {3})', 4326)),true) as temperature FROM apom.satresampmod07temperature where aqstime > '2010-01-01 00:00:00'::timestamp) as mod07temp ON (mod04.aqstime = mod07temp.aqstime) inner join(SELECT aqstime, st_neighborhood(rasref, (st_GeomFromText('POINT({4} {5})', 4326)), 2, 2, true) as pressure FROM apom.satresampressure where aqstime > '2010-01-01 00:00:00'::timestamp) as mod07press ON (mod04.aqstime = mod07press.aqstime) order by mod04.aqstime asc".format(location.y, location.x, location.y, location.x))                                                                                                                                                                                                                   
          
          
            cur.execute("SELECT myd04.aqstime, myd07temp.aqstime as temp_aqstime, myd07press.aqstime as press_aqstime,myd04.aod, myd07temp.temperature,myd07press.pressure from (SELECT aqstime, st_neighborhood(rasref, (st_GeomFromText('POINT({0} {1})', 4326)), 2, 2, true) as aod FROM apom.satresampmyd04 where aqstime > '2010-01-01 00:00:00'::timestamp) as myd04 inner join (SELECT aqstime, ST_Value(rasref, (st_GeomFromText('POINT({2} {3})', 4326)),true) as temperature FROM apom.satresampmyd07temperature where aqstime > '2010-01-01 00:00:00'::timestamp) as myd07temp ON (myd04.aqstime = myd07temp.aqstime) inner join(SELECT aqstime, ST_Value(rasref, (st_GeomFromText('POINT({4} {5})', 4326)),true) as pressure FROM apom.satresampmyd07pressure where aqstime > '2010-01-01 00:00:00'::timestamp) as myd07press ON (myd04.aqstime = myd07press.aqstime) order by myd04.aqstime asc".format(location.y, location.x, location.y, location.x,location.y, location.x))                                                                                                                                                                                                                   
          
          
          
          
            rows = cur.fetchall()
            for row in rows:
                aqstime = datetime.datetime.strptime(str(row["aqstime"]), "%Y-%m-%d %H:%M:%S") + timedelta(hours=7)
                temp_aqstime = datetime.datetime.strptime(str(row["temp_aqstime"]), "%Y-%m-%d %H:%M:%S") + timedelta(hours=7)
                press_aqstime = datetime.datetime.strptime(str(row["press_aqstime"]), "%Y-%m-%d %H:%M:%S") + timedelta(hours=7)

                # du lieu gia ve la 1 mang 2 chieu
                aod = row["aod"]
                aod_result = []
                for i in range(len(aod)):
                    # filter nhung gia tri != None thi se them vao mang ket qua
                    tmp = filter(lambda a: a != None, aod[i])
                    aod_result += tmp
                # calculate real aod value
                aod_value = (np.median(aod_result) * 0.00100000004749745) if len(aod_result) > 0 else 0
                    
                temperature = row["temperature"]
                if(temperature!=None):
                    temperature_value = (temperature + 15000)* 0.00999999977648258
                else:
                    temperature_value=0;
                """
               
                temperature_result = []
                for i in range(len(temperature)):
                    tmp = filter(lambda a: a != None, temperature[i])
                    temperature_result += tmp
                temperature_value = (np.median(temperature_result) + 15000) * 0.00999999977648258 if len(temperature_result) > 0 else 0
                """
                
                pressure = row["pressure"]
                if(pressure!=None):
                    pressure_value = pressure
                else:
                    pressure_value=0;

                
                #pressure_result = []
                # for i in range(len(pressure)):
                #     tmp = filter(lambda a: a != None, pressure[i])
                #     pressure_result += tmp
                # pressure_value = np.median(pressure_result) if len(pressure_result) > 0 else 0

                if temperature_value > 0 and aod_value > 0 and pressure_value > 0:
                    #limit temp and presure
                    #cur.execute("SELECT avg(pm1), avg(pm25), avg(pm10) FROM apom.grdpmhiscem_data WHERE (pm1 > 0 OR pm25 > 0 OR pm10 > 0) and aqstime > '{0}'::timestamp - interval '{1} minutes' and aqstime < '{2}'::timestamp + interval '{3} minutes' and stationid = {4} GROUP BY aqstime".format(aqstime, time_range, aqstime, time_range, stationid))
                    # unlimit
                    cur.execute("SELECT avg(pm1), avg(pm25), avg(pm10),avg(temp), avg(barometer),avg(radiation),avg(windspd),avg(winddir) FROM apom.grdpmhiscem_data WHERE (pm1 > 0 OR pm25 > 0 OR pm10 > 0) and aqstime > '{0}'::timestamp - interval '{1} minutes' and aqstime < '{2}'::timestamp + interval '{3} minutes' and stationid = {4} GROUP BY aqstime".format(aqstime, time_range, aqstime, time_range, stationid))
                    
                    pm_row = cur.fetchone()
                    if not(pm_row is None):
                        pm1 = pm_row[0]
                        pm25 = pm_row[1]
                        pm10 = pm_row[2]
                        temp = pm_row[3]
                        barometer=pm_row[4]
                        radiation = pm_row[5]
                        windspd = pm_row[6]
                        winddir = pm_row[7]

                        f.write("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15}\n".format(stationid, aqstime.year, aqstime.month, aqstime, temp_aqstime, pm1, pm25, pm10, aod_value, temperature_value,temp,pressure_value,barometer,radiation,windspd,winddir))
            
    except psycopg2.DatabaseError, e:
        print 'Error %s' % e    
        sys.exit(1)
    finally:
        if con:
            con.close()
        if f:
            f.close()

if __name__ == '__main__':

    join_mod_temp(30)
    join_myd_temp(30)